﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using YT.DB;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Collections;
 
namespace YT.Commons
{
    public class DataFunctions
    {
        #region public DataFunctions()
        public DataFunctions()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        #endregion

       
        #region GetFieldValue: Get a field value according to a table name and a condition
        /// <summary>
        /// Get a field value according to a table name and condition
        /// </summary>
        /// <param name="strTableName">Table name</param>
        /// <param name="strFieldName"> Field name</param>
        /// <param name="strCondition">Query condition</param>
        /// <returns>string value</returns>
        public static string GetFieldValue(DBAccess dBAccess, string strTableName, string strFieldName, string strCondition)
        {
            string strSql = "SELECT top 1 {0} FROM {1} WHERE {2}";
            string strReturn = "";
            DBAccess dbAccess;

            if (dBAccess == null)
                dbAccess = new DBAccess();
            else
                dbAccess = dBAccess;

            if (strCondition.Trim() == "")
                strCondition = "1=1";

            strSql = String.Format(strSql, strFieldName, strTableName, strCondition);

            strReturn = dbAccess.GetValue(strSql);

            if (strReturn == null)
                strReturn = "";

            return strReturn.TrimEnd();
        }


        public static string GetFieldValue(string strTableName, string strFieldName, string strCondition)
        {
            return GetFieldValue(null, strTableName, strFieldName, strCondition);
        }

        public static object GetFieldObjectValue(DBAccess dBAccess, string strTableName, string strFieldName, string strCondition)
        {
            string strSql = "SELECT {0} FROM {1} WHERE {2}";
            DBAccess dbAccess;

            if (dBAccess == null)
                dbAccess = new DBAccess();
            else
                dbAccess = dBAccess;

            strSql = String.Format(strSql, strFieldName, strTableName, strCondition);

            return dbAccess.GetObjectValue(strSql);
        }


        #endregion

        #region SetmMiscValue: Set switch string value with mMiscSwitch table according to Modual name and swtich name
        public static bool SetmMiscValue(DBAccess dBAccess, string strSwitchName,
            string strSwitchValue, string strModualName)
        {
            bool bSetmMiscValue;

            string strSQl;
            DataTable dtMisc;

            const string strModualNameField = "SystemName";
            const string strSwitchNameField = "SwitchName";
            const string strSwitchValueField = "SwitchValue";
            const string strSwtichTable = "mMiscSwitch";
            const string strTypeField = "Type";

            DBAccess dBAccessCur;

            if (dBAccess == null)
                dBAccessCur = new DBAccess();
            else
                dBAccessCur = dBAccess;

            bSetmMiscValue = false;

            strSwitchName = strSwitchName.Trim();
            strModualName = strModualName.Trim();
            strSwitchValue = strSwitchValue.Trim();

            try
            {
                strSQl = "SELECT " + strSwitchValueField + " FROM " + strSwtichTable;
                strSQl += " WHERE " + strModualNameField + " = '" + strModualName + "'";
                strSQl += " AND " + strSwitchNameField + " = '" + strSwitchName + "'";

                dtMisc = new DataTable();
                dtMisc = dBAccessCur.GetDataSet(strSQl).Tables[0];

                if (dtMisc.Rows.Count > 0)
                {
                    strSQl = "UPDATE " + strSwtichTable;
                    strSQl += " SET " + strSwitchValueField + " = '" + strSwitchValue + "'";
                    strSQl += " WHERE " + strModualNameField + " = '" + strModualName + "'";
                    strSQl += " AND " + strSwitchNameField + " = '" + strSwitchName + "'";
                    dBAccessCur.ExecuteSql(strSQl);
                    bSetmMiscValue = true;
                }
                else
                {
                    //MsgBox "Misc not exist!", vbExclamation, SysVar.WarnTitle
                    bSetmMiscValue = false;
                }

                if (dBAccess == null)
                    dBAccessCur.CloseCon();
            }
            catch (Exception ex)
            {
                bSetmMiscValue = false;
                throw new Exception(ex.Message);
            }

            return bSetmMiscValue;
        }
        #endregion

        #region GetmMiscValue: Get switch string value from mMiscSwitch table according to Modual name and swtich name
        /// <summary>
        /// Get switch value form mMiscSwitch table according to a Modual name and a swtich name
        /// </summary>
        /// <param name="strSwitchName">Switch Name</param>
        /// <param name="strModualName">Module name</param>
        /// <returns>switch value,if null return ""</returns>
        public static string GetmMiscValue(DBAccess dBAccess, string strSwitchName, string strModualName)
        {
            string strSql = string.Empty;
            string strReturn = string.Empty;
            string strType = string.Empty;
            string strErrorMessage = string.Empty;
            DataSet dataSet = new DataSet();
            DBAccess dbAccess = null;

            if (dBAccess == null)
                dbAccess = new DBAccess();
            else
                dbAccess = dBAccess;

            strSql = "SELECT switchValue,Type FROM mMiscSwitch WHERE SystemName='{0}' AND SwitchName='{1}'";
            strSql = string.Format(strSql, strModualName, strSwitchName);
            dataSet = dbAccess.GetDataSet(strSql);

            if (dataSet.Tables[0].Rows.Count > 0)
            {
                DataRow DR = dataSet.Tables[0].Rows[0];
                strReturn = DR[0].ToString().TrimEnd();
                strType = DR[1].ToString().TrimEnd().ToLower();
                switch (strType)
                {
                    case "numeric":
                        strReturn = strReturn.Replace(",", "").Trim();
                        if (!BaseFunctions.IsNumeric(strReturn))
                        {
                            strReturn = "";
                            strErrorMessage = "The switch " + strSwitchName + " setting error,It must be numeric type.";
                        }
                        break;
                    case "char":
                        strReturn = strReturn.TrimEnd();

                        break;
                    case "date":
                        if (!BaseFunctions.IsDate(strReturn))
                        {
                            strErrorMessage = "Invalid date.The switch " + strSwitchName + " setting error.";
                            strReturn = DateTime.Now.ToShortDateString();
                        }
                        break;
                    default:
                        strReturn = string.Empty;
                        break;
                }
                if (strErrorMessage != string.Empty)
                {
                    throw new Exception(strErrorMessage);
                }
            }
            else
            {
                UpdatemMissSwitch(strSwitchName, strModualName);
                strReturn = "";
            }

            if (dBAccess == null)
                dbAccess.CloseCon();
            return strReturn;
        }

        public static string GetmMiscValue(string strSwitchName, string strModualName)
        {
            return GetmMiscValue(null, strSwitchName, strModualName);
        }

        public static string GetmMiscValue(string strSwitchName)
        {
            return GetmMiscValue(null, strSwitchName, "");
        }

        private static void UpdatemMissSwitch(string strSwitchName, string strModualName)
        {
            string strSql = "";
            int iCount = 0;

            DBAccess dBAccess = new DBAccess(false, false);

            strSql = "SELECT COUNT(*) FROM mMissSwitch WHERE SwitchSys='" + strModualName + "' AND SwitchName='" + strSwitchName + "'";
            iCount = int.Parse(dBAccess.GetValue(strSql));
            if (iCount == 0)
            {
                strSql = "INSERT INTO mMissSwitch (SwitchSys,SwitchName) VALUES('" + strModualName + "','" + strSwitchName + "')";
                dBAccess.ExecuteSql(strSql);
            }

            dBAccess.CloseCon();
        }
        #endregion

        #region GetmSwitchValue: Get switch bool value from mSwitch table according to Modual name and swtich name
        /// <summary>
        /// Get switch value form mMiscSwitch table according to a Modual name and a swtich name
        /// </summary>
        /// <param name="strSwitchName">Switch Name</param>
        /// <param name="strModualName">Module name</param>
        /// <returns>switch value,if null false ""</returns>
        public static bool GetmSwitchValue(DBAccess dBAccess, string strSwitchName, string strModualName)
        {
            string strSql = string.Empty;
            bool bReturn = false;
            string strErrorMessage = string.Empty;
            string strReturn = "";
            DBAccess dbAccess = null;

            if (dBAccess == null)
                dbAccess = new DBAccess();
            else
                dbAccess = dBAccess;

            strSql = "Select switchValue from mSwitch where SwitchSys='{0}' and SwitchName='{1}'";
            strSql = string.Format(strSql, strModualName, strSwitchName);

            strReturn = dbAccess.GetValue(strSql);
            if (strReturn == null)
            {
                bReturn = false;
                UpdatemMissSwitch(strSwitchName, strModualName);
            }
            else
            {
                bReturn = bool.Parse(strReturn);
            }
            return bReturn;
        }

        public static bool GetmSwitchValue(string strSwitchName, string strModualName)
        {
            return GetmSwitchValue(null, strSwitchName, strModualName);
        }
        #endregion

        #region GetRecStamp: Get RecStamp According Table Name and RecId
        public static byte[] GetRecStamp(string strTableName, string strRecId)
        {
            return GetRecStamp(null, strTableName, strRecId);
        }

        public static byte[] GetRecStamp(DBAccess dBAccess, string strTableName, string strRecId)
        {
            string strSql = "SELECT RecStamp FROM {0} WHERE RECID={1}";
            object oRecStamp;
            DBAccess dbAccess;

            if (dBAccess == null)
                dbAccess = new DBAccess();
            else
                dbAccess = dBAccess;

            strSql = string.Format(strSql, strTableName, strRecId);
            oRecStamp = dbAccess.GetObjectValue(strSql);
            if (oRecStamp == null)
                return null;
            else
                return (byte[])oRecStamp;

        }
        #endregion

        #region MakeLookUpGridSQL
        public static string MakeLookUpGridSQL(string strSql)
        {
            string strMakeSql = "select recid,";
            string[] FieldArray = BaseFunctions.FromSqlToArray(strSql);
            string strTableName = BaseFunctions.GetSqlParts(strSql, SqlOption.SqlPartTableName);

            if (FieldArray.Length == 1 && FieldArray[0].Trim() == "*")
            {
                DBAccess db = new DBAccess();
                DataSet ds = db.GetDataSet("select top 0 * from " + strTableName);
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    if (ds.Tables[0].Columns[i].ColumnName.Trim().ToUpper() != "RECID" && ds.Tables[0].Columns[i].ColumnName.Trim().ToUpper() != "[RECID]" && ds.Tables[0].Columns[i].ColumnName.Trim().ToUpper() != "RECSTAMP" && ds.Tables[0].Columns[i].ColumnName.Trim().ToUpper().IndexOf("AS RECID") == -1)
                        strMakeSql += ds.Tables[0].Columns[i].ColumnName.Trim() + ",";
                }
                db.CloseCon();

            }
            else
            {
                for (int i = 0; i < FieldArray.Length; i++)
                {
                    if (FieldArray[i].Trim().ToUpper() != "RECID" && FieldArray[i].Trim().ToUpper() != "[RECID]" && FieldArray[i].Trim().ToUpper() != "RECSTAMP" && FieldArray[i].Trim().ToUpper().IndexOf("AS RECID") == -1 && FieldArray[i].Trim().ToUpper().IndexOf(".RECID") == -1)
                        strMakeSql += FieldArray[i].Trim() + ",";
                    else if (FieldArray[i].Trim().ToUpper().IndexOf(" RECID") != -1 || FieldArray[i].Trim().ToUpper().IndexOf("AS RECID") != -1 || FieldArray[i].Trim().ToUpper().IndexOf(".RECID") != -1)
                        strMakeSql = strMakeSql.Replace("select recid,", "select " + FieldArray[i].Trim() + ",");
                }
            }

            strMakeSql = strMakeSql.Substring(0, strMakeSql.Length - 1);


            int intFrom = strSql.IndexOf(" from ", StringComparison.CurrentCultureIgnoreCase);

            strMakeSql = strMakeSql + strSql.Substring(intFrom);

            return strMakeSql;


        }
        #endregion

        #region GetFieldType: Get Field Type According to table name and field name
        /// <summary>
        /// Get Field Type According to table name and field name
        /// </summary>
        /// <param name="dBAccess"></param>
        /// <param name="strTableName"></param>
        /// <param name="strFieldName"></param>
        /// <returns></returns>
        public static DataType GetFieldType(DBAccess dBAccess, string strTableName, string strFieldName)
        {
            string strSql = "SELECT TOP 0 {0} FROM {1}";
            DBAccess dbAccess;
            DataSet DS = new DataSet();



            if (dBAccess == null)
                dbAccess = new DBAccess();
            else
                dbAccess = dBAccess;

            strSql = string.Format(strSql, strFieldName, strTableName);
            DS = dbAccess.GetDataSet(strSql);

            return BaseFunctions.ConvertSqlDataType(DS.Tables[0].Columns[0].DataType.Name);
        }

        public static DataType GetFieldType(string strTableName, string strFieldName)
        {
            return GetFieldType(null, strTableName, strFieldName);
        }
        #endregion

        
        #region DelTemporyTable:Delete Tempory Table
        public static void DelTemporyTable(DBAccess dBAccess, string strTempTableName)
        {
            string strSql = "";
            if (strTempTableName.Substring(0, 1) != "#")
                strTempTableName = "#" + strTempTableName;

            strTempTableName = strTempTableName.Trim();

            strSql = @"IF EXISTS (SELECT * FROM tempdb..sysobjects  WHERE  Name like '{0}%')  DROP TABLE tempdb..{0}";
            strSql = string.Format(strSql, strTempTableName);

            try
            {
                dBAccess.ExecuteSql(strSql);
            }
            catch
            {
                //deal with that  the table doesn't exist 
            }
        }
        #endregion

        
       

        #region check if the record exists in this table
        public static bool CheckRecordExist(DBAccess dBAccess, string strTableName, string strFieldvalue, string strFieldName, string strCondition)
        {
            DBAccess dbAccess;

            if (dBAccess == null)
                dbAccess = new DBAccess();
            else
                dbAccess = dBAccess;
            string strSql = "";
            string strSubCondition = "";

            strFieldvalue = BaseFunctions.SpecialCharDeal(strFieldvalue);
            if (strCondition != "")
            {
                strSubCondition = " and " + strCondition;
            }

            strSql = "SELECT top 1 " + strFieldName + " FROM " + strTableName + " WHERE " + strFieldName + " = '" + strFieldvalue + "' " + strSubCondition;

            if (dbAccess.GetDataFirstRow(strSql) != null)
                return true;
            else
                return false;


        }
        public static bool CheckRecordExist(string strTableName, string strFieldvalue, string strFieldName, string strCondition)
        {
            return CheckRecordExist(null, strTableName, strFieldvalue, strFieldName, strCondition);
        }
        public static bool CheckRecordExist(string strTableName, string strFieldvalue, string strFieldName)
        {
            return CheckRecordExist(null, strTableName, strFieldvalue, strFieldName, "");
        }
        public static bool CheckRecordExist(DBAccess dBAccess, string strTableName, string strFieldvalue, string strFieldName)
        {
            return CheckRecordExist(dBAccess, strTableName, strFieldvalue, strFieldName, "");
        }
        #endregion

       
        #region GetMaxMinRec: get  tablename's some fieldname max value and min value
        public static void GetMaxMinRec(DBAccess dBAccess, string strTableName, string strFieldName, ref string strMaxValue, ref string strMinValue, string strCondition)
        {
            string strSql = "";
            DataSet ds = new DataSet();
            DataRow dr;

            if (dBAccess == null)
            {
                dBAccess = new DBAccess();
            }

            strSql = "SELECT MIN(" + strFieldName + ") AS a, MAX(" + strFieldName + ") AS b From " + strTableName;
            if (strCondition != "")
            {
                strSql += " Where " + strCondition;
            }

            ds = dBAccess.GetDataSet(strSql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                dr = ds.Tables[0].Rows[0];
                if (dr["a"] != DBNull.Value)
                    strMinValue = dr["a"].ToString();
                else
                    strMinValue = "";

                if (dr["b"] != DBNull.Value)
                    strMaxValue = dr["b"].ToString();
                else
                    strMaxValue = "";
            }
            else
            {
                strMinValue = "";
                strMaxValue = "";
            }
        }
        public static void GetMaxMinRec(DBAccess dBAccess, string strTableName, string strFieldName, ref string strMaxValue, ref string strMinValue)
        {
            GetMaxMinRec(dBAccess, strTableName, strFieldName, ref  strMaxValue, ref  strMinValue, "");
        }
        public static void GetMaxMinRec(string strTableName, string strFieldName, ref string strMaxValue, ref string strMinValue, string strCondition)
        {
            GetMaxMinRec(null, strTableName, strFieldName, ref  strMaxValue, ref  strMinValue, strCondition);
        }
        #endregion

        public static string GetTableSQL(DBAccess dbcon, string strTableName)
        {
            DataTable dt = dbcon.GetDataSet("select top 0 * from  " + strTableName).Tables[0];
            string strFields = "";
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Columns[i].Caption.ToUpper().Trim() != "Recid".ToUpper() && dt.Columns[i].Caption.ToUpper().Trim() != "RecStamp".ToUpper())
                {
                    strFields += (strFields != "" ? "," : "") + "[" + dt.Columns[i].Caption + "]";
                }
            }
            return strFields;

        }
        #region GetTopRecord: Get filed value of first or last record
        public static string GetTopRecord(DBAccess dBAccess, string strTableName, string strFieldName, string strCondition, bool bFirstRecord)
        {
            string strSql = "", strValue = ""; ;

            if (strCondition.Trim() != "")
            {
                strCondition = " where " + strCondition;
            }

            if (bFirstRecord)
                strSql = "select top 1 " + strFieldName + " from " + strTableName + strCondition + " order by " + strFieldName;
            else
                strSql = "select top 1 " + strFieldName + " from " + strTableName + strCondition + " order by " + strFieldName + " desc";

            if (dBAccess == null)
            {
                dBAccess = new DBAccess();
            }

            strValue = dBAccess.GetValue(strSql);

            if (strValue == null)
            {
                strValue = "";
            }

            return strValue;
        }
        public static string GetTopRecord(DBAccess dBAccess, string strTableName, string strFieldName, string strCondition)
        {
            return GetTopRecord(dBAccess, strTableName, strFieldName, strCondition, true);
        }
        public static string GetTopRecord(string strTableName, string strFieldName, string strCondition)
        {
            return GetTopRecord(null, strTableName, strFieldName, strCondition, true);
        }
        public static string GetTopRecord(DBAccess dBAccess, string strTableName, string strFieldName)
        {
            return GetTopRecord(dBAccess, strTableName, strFieldName, "", true);
        }
        public static string GetTopRecord(string strTableName, string strFieldName)
        {
            return GetTopRecord(null, strTableName, strFieldName, "", true);
        }
        #endregion

        #region GetDataRow: Get One DataRow according to table name and condition
        public static DataRow GetDataRow(DBAccess dBAccess, string strTableName, string strFields, string strCondition)
        {
            string strSql = "";
            DataSet ds = new DataSet();

            if (dBAccess == null)
            {
                dBAccess = new DBAccess();
            }
            if (strCondition.Trim() != "")
                strSql = "SELECT top 1 " + strFields + " FROM " + strTableName + " Where " + strCondition;
            else
                strSql = "SELECT top 1 " + strFields + " FROM " + strTableName;

            ds = dBAccess.GetDataSet(strSql);

            if (ds != null && ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0].Rows[0];
            else
                return null;
        }

        public static DataRow GetDataRow(string strTableName, string strFields, string strCondition)
        {
            return GetDataRow(null, strTableName, strFields, strCondition);
        }
        public static DataRow GetDataRow(DBAccess dBAccess, string strTableName, string strCondition)
        {
            return GetDataRow(dBAccess, strTableName, "*", strCondition);
        }
        public static DataRow GetDataRow(string strTableName, string strCondition)
        {
            return GetDataRow(null, strTableName, "*", strCondition);
        }
        #endregion

       
    

     
        /// <summary>
        /// Length of return arry is 11: 0~3 is AgedDay, 4~11 is Balance amount
        /// </summary>
        /// <param name="dBAccess"></param>
        /// <param name="strStCustNo"></param>
        /// <param name="strEdCustNo"></param>
        /// <param name="strCutDate"></param>
        /// <param name="bAgedByDocDate"></param>
        /// <param name="bIncludeInterest"></param>
        /// <param name="strCurNo"></param>
        /// <returns></returns>
       
      

        #region UserOnline
        public static bool UserOnline(DBAccess dbcon, string sUsername, string sDatabaseName)
        {


            DataView dv = new DataView(dbcon.GetDataSet("select * from mcomputer where active=1 and username ='" + sUsername + "'").Tables[0]);
            if (dv == null || dv.Count == 0)
            {
                return false;
            }
            DataView dvwho = new DataView(dbcon.GetDataSet("sp_who2").Tables[0]);
            for (int i = 0; i < dv.Count; i++)
            {
                dvwho.RowFilter = "Hostname='" + dv[i]["ComputerName"].ToString() + "' and dbname='" + sDatabaseName + "' and programname<>'WoodPro2000 Daily Job'";
                if (dvwho.Count > 0)
                {
                    return true;
                }
            }
            return true;
        }
        #endregion

        public static MenuInfo GetMenuInfo(string strMenuId)
        {
            DBAccess dbcon = new DBAccess(false, false);
            MenuInfo miMenuInfo = GetMenuInfo(dbcon, strMenuId);
            dbcon.CloseCon();
            return miMenuInfo;
        }
        public static MenuInfo GetMenuInfo(DBAccess dbcon, string strMenuId)
        {
            DataRow drMenu = dbcon.GetDataFirstRow("select top 1 * from mMenu where MenuId='" + strMenuId + "'");
            MenuInfo miMenuInfo = new MenuInfo();
            miMenuInfo.strMenuId = "";
            miMenuInfo.strMenuName = "";
            miMenuInfo.strProgname = "";
            miMenuInfo.strTableName = "";
            miMenuInfo.strNameSpace = "WP.Modules";
            miMenuInfo.strPermit = "Y";
            miMenuInfo.iParameter = 0;
            miMenuInfo.iPasswordLevel = 0;
            miMenuInfo.strOverViewCondition = "";

            if (drMenu != null)
            {
                miMenuInfo.strMenuId = drMenu["MenuId"].ToString();
                //miMenuInfo.iParameter = CvtFunc.ToInt(drMenu["Net_Parameter"]);
                miMenuInfo.strMenuName = CvtFunc.ToString(drMenu["MenuName"]);
                miMenuInfo.strProgname = CvtFunc.ToString(drMenu["Progname"]);
                //miMenuInfo.iPasswordLevel = CvtFunc.ToInt(drMenu["PasswordLevel"]);
                miMenuInfo.strNameSpace = CvtFunc.ToString(drMenu["NameSpace"]).Trim();                
            }
            return miMenuInfo;
        }
        
        public static string GetSqlByDescriptionSort(string strSql)
        {
            string strOrdBy = "";
            string strSqlMain = BaseFunctions.GetSqlParts(strSql, SqlOption.SqlPartMain);
            string strSqlWhere = BaseFunctions.GetSqlParts(strSql, SqlOption.SqlPartWhere);
            string strSqlTable = BaseFunctions.GetSqlParts(strSql, SqlOption.SqlPartTableName);
            string strSqlInnerJoin = BaseFunctions.GetSqlParts(strSql, SqlOption.SqlPartInner);
            string strSqlOrderby = BaseFunctions.GetSqlParts(strSql, SqlOption.SqlPartOrderBy);
            string[] strFields = null; //BaseFunctions.GetFieldsStringFromSQL(strSql).Split(new char[] { ',' });
            DBAccess dbcon = null;

            if (strSql.IndexOf("*") != -1)
            {
                dbcon = new DBAccess(false, false);
                strSql = "select " + DataFunctions.GetTableSQL(dbcon, strSqlTable) + " from " + strSqlTable + strSqlInnerJoin + " " + strSqlWhere + strSqlOrderby;
                dbcon.CloseCon();
            }
            BaseFunctions.GetSqlParts(strSql, SqlOption.SqlPartMain, ref strFields);

            #region deal fields
            int i = 0, iASPosition = -1;

            if (strFields != null)
            {
                for (i = 0; i < strFields.Length; i++)
                {
                    iASPosition = strFields[i].IndexOf(" as ", StringComparison.CurrentCultureIgnoreCase);
                    if (iASPosition != -1)
                    {
                        strFields[i] = strFields[i].Substring(iASPosition + 4).Trim();
                    }
                }
            }
            #endregion

            int iFirst = 1;
            int iSecond = 2;

            if (strSqlTable.Trim().ToUpper() == "GlAccount".ToUpper())
            {
                iFirst = 3;
                iSecond = 4;
            }
            ////if (strFields.Length == 1 && strFields[0].Trim() == "*")
            ////{
            ////    dbcon = new DBAccess(false, false);
            ////    strFields = DataFunctions.GetTableSQL(dbcon, strSqlTable).Split(new char[] { ',' });
            ////    dbcon.CloseCon();
            ////}

            if (strFields.Length >= 2)
            {
                if (strFields[0].ToUpper().Trim().Replace("[", "").Replace("]", "") == "RECID")
                {
                    if (strFields.Length >= 3)
                    {
                        strOrdBy = "Order By " + strFields[iSecond];
                    }
                }
                else
                {
                    strOrdBy = "Order By " + strFields[iFirst];
                }
            }

            if (strOrdBy == "")
                return strSql;
            else
                return strSqlMain + " " + strSqlInnerJoin + " " + strSqlWhere + " " + strOrdBy.Replace("[", "").Replace("]", "");

        }
        public static string GetTablePrimaryKeyFields(DBAccess dbcon, string strTableName)
        {
            string strKeyFields = "";
            DataView dvw = dbcon.GetDataSet("exec sp_pkeys  @table_name ='" + strTableName + "'").Tables[0].DefaultView;
            foreach (DataRowView drw in dvw)
            {
                strKeyFields += drw["Column_Name"].ToString().TrimEnd() + ",";
            }

            if (strKeyFields.Length > 1) strKeyFields = strKeyFields.Substring(0, strKeyFields.Length - 1);
            return strKeyFields;
        }

        public static string GetLookUpSQL(string strTableName, string strPara)
        {
            string strSQL = string.Empty;
            DBAccess dbcon = new DBAccess(false, false);
            strSQL ="select * from mTableDict where TableName ='"+ strTableName +"' and parameter='"+ strPara +"'";
            DataRow drRow = dbcon.GetDataFirstRow(strSQL);
            if (drRow != null)
                return CvtFunc.ToString( drRow["SQLLIST"]);
            else
                return string.Empty;

            dbcon.CloseCon();  
        }

        public static string GetUpdateSQL(string strTableName, string strPara)
        {
            string strSQL = string.Empty;
            DBAccess dbcon = new DBAccess(false, false);
            strSQL = "select * from mTableDict where TableName ='" + strTableName + "' and parameter='" + strPara + "'";
            DataRow drRow = dbcon.GetDataFirstRow(strSQL);
            if (drRow != null)
                return CvtFunc.ToString(drRow["SQLUpdate"]);
            else
                return string.Empty;

            dbcon.CloseCon();
        }

        public static string GetSqlByKeySort(string strSql)
        {
            string strOrdBy = "";
            string strSqlMain = BaseFunctions.GetSqlParts(strSql, SqlOption.SqlPartMain);
            string strSqlWhere = BaseFunctions.GetSqlParts(strSql, SqlOption.SqlPartWhere);
            string strSqlTable = BaseFunctions.GetSqlParts(strSql, SqlOption.SqlPartTableName);
            string strSqlInnerJoin = BaseFunctions.GetSqlParts(strSql, SqlOption.SqlPartInner);

            DBAccess dbcon = new DBAccess(false, false);
            string strKeyField = GetTablePrimaryKeyFields(dbcon, strSqlTable);
            if (strKeyField == "")
            {
                string[] strFields = BaseFunctions.GetFieldsStringFromSQL(strSql).Split(new char[] { ',' });
                if (strFields.Length == 1 && strFields[0].Trim() == "*")
                {
                    strFields = DataFunctions.GetTableSQL(dbcon, strSqlTable).Split(new char[] { ',' });
                    dbcon.CloseCon();
                }
                if (strFields.Length >= 2)
                {
                    if (strFields[0].ToUpper().Trim().Replace("[", "").Replace("]", "") == "RECID")
                    {
                        if (strFields.Length >= 3)
                        {
                            strOrdBy = "Order By " + strFields[1];
                        }
                    }
                    else
                    {
                        strOrdBy = "Order By " + strFields[0];
                    }
                }
            }
            else
            {
                strOrdBy = " Order By " + strKeyField;
            }

            if (strOrdBy == "")
                return strSql;
            else
                return strSqlMain + " " + strSqlInnerJoin + " " + strSqlWhere + " " + strOrdBy.Replace("[", "").Replace("]", "");

        }

        public static bool InsertIntoMsgTable()
        {
            DBAccess dbcon  = new DBAccess ( false,false );
            return InsertIntoMsgTable(dbcon);
        }

        public static bool SendReutrnMsg(System.Data.OleDb.OleDbConnection mdbcon,string strSendTo, string strSickName )//发送回执
        {
            string strSendMsg = "您的短信已收到,谢谢!";
            System.Threading.Thread.Sleep(200);
            string strSQL = "insert into outbox(Mbno,Msg,username) values( '" + strSendTo + "',  '" + strSendMsg + "','" + strSickName + "' )";
            System.Data.OleDb.OleDbCommand mcomm = new System.Data.OleDb.OleDbCommand(strSQL, mdbcon);
            mcomm.ExecuteNonQuery();
            return true;
        }

        public static  bool InsertIntoMsgTable( DBAccess mdbcon )
        {
            string strConnectionPara = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + DataFunctions.GetApplication() + "\\data.mdb'";
            System.Data.OleDb.OleDbConnection mcon = new System.Data.OleDb.OleDbConnection(strConnectionPara);
            mcon.Open();

            string strRecvMsg = string.Empty;
            string strMsgType = string.Empty;
            DataTable dtInsert =new DataTable () ;
            string strSQL = "select * from inbox where readed=0";
            System.Data.OleDb.OleDbDataAdapter adf = new System.Data.OleDb.OleDbDataAdapter(strSQL,mcon);
            adf.Fill(dtInsert);
            foreach (DataRow drRow in dtInsert.Rows)
            {
                string strMobNo = CvtFunc.ToString(drRow["mbNo"]);
                if (strMobNo.StartsWith("86")) strMobNo = strMobNo.Replace("86", "");
                strSQL = "select * from msickPerson where MobilePhone='" + strMobNo + "'";
                DataRow drSickPerson = mdbcon.GetDataFirstRow(strSQL);
                if (drSickPerson != null)
                {
                    string strUserName = CvtFunc.ToString(drSickPerson["SickName"]);
                    string strRecID = CvtFunc.ToString(drSickPerson["SickRecID"]);                   
                    strRecvMsg = CvtFunc.ToString(drRow["Msg"]).Trim();
                    strRecvMsg = strRecvMsg.Replace("    ", "");
                    strRecvMsg = strRecvMsg.Replace("   ", "");
                    strRecvMsg = strRecvMsg.Replace("  ", "");
                    string[] strMsg = BaseFunctions.Split(strRecvMsg , " ");                     
                    if (strMsg.Length >= 2) // 是 提问回复短信
                    {   
                        strMsgType = "N";
                        if (strMsg.Length == 2)
                        {
                            strSQL = @"Insert Into SmsInbox ( SickName,MobilePhone,RecvDate,Message,MsgType,SickRecID,Title1,Title2) 
                           VALUES ( '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}') ";
                            strSQL = string.Format(strSQL, strUserName, strMobNo, drRow["ArriveTime"], drRow["Msg"], strMsgType, strRecID, strMsg[0], strMsg[1]);
                        }
                        else if (strMsg.Length == 4)
                        {
                            strSQL = @"Insert Into SmsInbox ( SickName,MobilePhone,RecvDate,Message,MsgType,SickRecID,Title1,Title2,Title3,Title4) 
                           VALUES ( '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8}') ";
                            strSQL = string.Format(strSQL, strUserName, strMobNo, drRow["ArriveTime"], drRow["Msg"], strMsgType, strRecID , strMsg[0], strMsg[1], strMsg[2], strMsg[3]);
                        }
                        mdbcon.ExecuteSql(strSQL);
                        SendReutrnMsg(mcon, strMobNo, strUserName );
                    }
                    else if (strMsg.Length  == 1 && strMsg[0].ToString().Trim().ToUpper().StartsWith ("X"))
                    {
                        strMsgType = "X";

                        strRecID = DataFunctions.GetFieldValue("msickPerson", "SickRecID", "MobilePhone='" + CvtFunc.ToString(strMobNo) + "'");
                        strUserName = DataFunctions.GetFieldValue("mSickperson", "SickName", "MobilePhone='" + CvtFunc.ToString(strMobNo) + "'");
                        strSQL = @"Insert Into SmsInbox ( SickName,MobilePhone,RecvDate,Message,MsgType,SickRecID) 
                           VALUES ( '{0}','{1}','{2}','{3}','{4}','{5}') ";
                        strSQL = string.Format(strSQL, strUserName, strMobNo, drRow["ArriveTime"], drRow["Msg"], strMsgType,strRecID );
                        mdbcon.ExecuteSql(strSQL);

                        strSQL = "update mSickPerson set NeedMsg=0 where SickRecID='" + strRecID + "'";
                        mdbcon.ExecuteSql(strSQL);

                        strSQL ="delete smsoutbox where sickRecID='"+ strRecID  +"'";
                        mdbcon.ExecuteSql ( strSQL);

                        SendReutrnMsg(mcon, strMobNo, strUserName);
                    }
                    else if (strRecvMsg.Length > 0 && strRecvMsg.Substring(0, 1).ToUpper() == "Q")
                    {
                        strMsgType = "Q";
                        strUserName = DataFunctions.GetFieldValue("mSickperson", "SickName", "MobilePhone='" + strMobNo + "'");
                        strSQL = @"Insert Into SmsInbox ( SickName,MobilePhone,RecvDate,Message,MsgType,SickRecID) 
                           VALUES ( '{0}','{1}','{2}','{3}','{4}','{5}') ";
                        strRecvMsg = strRecvMsg.Substring(1, strRecvMsg.Length-1);
                        strSQL = string.Format(strSQL, strUserName, strMobNo, drRow["ArriveTime"], strRecvMsg, strMsgType, strRecID );
                        mdbcon.ExecuteSql(strSQL);
                        SendReutrnMsg(mcon, strMobNo, strUserName);
                    }
                    else
                    {
                        strMsgType = "O";
                        strUserName = DataFunctions.GetFieldValue("mSickperson", "SickName", "MobilePhone='" + CvtFunc.ToString(drRow["mbNo"]) + "'");
                        strSQL = @"Insert Into SmsInbox ( SickName,MobilePhone,RecvDate,Message,MsgType,SickRecID) 
                           VALUES ( '{0}','{1}','{2}','{3}','{4}','{5}') ";
                        strSQL = string.Format(strSQL, strUserName, strMobNo, drRow["ArriveTime"], drRow["Msg"], strMsgType,strRecID );
                        mdbcon.ExecuteSql(strSQL);
                    }
                    strSQL = "update inbox set readed =1 where Id=" + CvtFunc.ToString(drRow["ID"]) + "";
                    System.Data.OleDb.OleDbCommand olecom = new System.Data.OleDb.OleDbCommand(strSQL, mcon);
                    olecom.ExecuteNonQuery();
                }
            }
            return true;
        }
        /// <summary>
        /// if the value of switch changes then write the change to database
        /// </summary>
        /// <param name="dBAccess"></param>
        /// <param name="bLogic"></param>
        /// <param name="strSwitchName"></param>
        /// <param name="strSysName"></param>
        /// <param name="OldValue"></param>
        /// <param name="NewValue"></param>
        public static void SaveSwitchValueChangeToDB(DBAccess dBAccess, bool bLogic, string strSwitchName, string strSysName, string OldValue, string NewValue)
        {
            string strSql = string.Empty;
            string strFields = string.Empty;
            DataTable dtLog = null;
            DataRow drLog = null;

            if (strSwitchName == string.Empty || strSysName == string.Empty) return;

            //The switch value does not change then exit sub
            if (OldValue.Trim() == NewValue.Trim()) return;

            strSql = "select * from mSwitchLog where SwitchName='" + strSwitchName.Trim();
            strSql += "'AND Usys='" + strSysName.Trim() + "'";
            dtLog = dBAccess.GetDataTable(strSql);

            drLog = dtLog.NewRow();
            drLog["Usys"] = strSysName;
            drLog["SwitchName"] = strSwitchName;
            if (bLogic)
                drLog["Logical"] = 1;
            else
                drLog["Logical"] = 0;

            drLog["UserName"] = SysVar.UserName;
            drLog["UDate"] = System.DateTime.Now;
            drLog["UTime"] = string.Format("{0:HH:mm}", DateTime.Now);
            drLog["OriginValue"] = OldValue;
            drLog["NewValue"] = NewValue;
            strFields = "Usys,SwitchName,Logical,UserName,UDate,UTime,OriginValue,NewValue";
            UpdateTable(dBAccess, strFields, "mSwitchLog", drLog, FormStatus.AddNew);
        }


        #region public static void UpdateTable() OverLoad +2
        /// <summary>
        /// Created at 7/26/2005
        /// function : Update the Complete data to DataBase Table 
        /// </summary>
        /// <param name="dBAccess"></param>
        /// <param name="strFields"></param>
        /// <param name="strTableName"></param>
        /// <param name="dr"></param>
        /// 

        public static void UpdateTable(DBAccess dBAccess, string strFields, string strTableName, DataRow dr, FormStatus status)
        {
            UpdateTable(dBAccess, strFields, strTableName, dr, status, "RecID", true);
        }
        public static void UpdateTable(DBAccess dBAccess, string strFields, string strTableName, DataRow dr)
        {
            UpdateTable(dBAccess, strFields, strTableName, dr, FormStatus.Update, "RecID", true);
        }
        public static void UpdateTable(DBAccess dBAccess, string strFields, string strTableName, DataRow dr, FormStatus status, string strKeyFieldsName, bool blnNeedGetTimeStamp)
        {
            string strSql = string.Empty;
            string strUpdateSql = string.Empty;
            if (strKeyFieldsName == "") strKeyFieldsName = "RecID";
            strSql = BaseFunctions.GenerateSqlForDataAccess(strFields, strTableName, dr, status, strKeyFieldsName, blnNeedGetTimeStamp);
            if (status == FormStatus.AddNew)
                dr["RECID"] = dBAccess.GetObjectValue(strSql);
            else
                if (blnNeedGetTimeStamp && CheckFieldInTable(dBAccess, "RecStamp", strTableName))
                    dr["RecStamp"] = dBAccess.GetObjectValue(strSql);
                else
                {
                    if (strSql.Split(';').Length > 1 && strSql.ToUpper().IndexOf("RECSTAMP") > 0)
                        strUpdateSql = strSql.Replace(strSql.Split(';')[strSql.Split(';').Length - 1].ToString(), "");
                    else
                        strUpdateSql = strSql;
                    dBAccess.ExecuteSql(strUpdateSql);
                }
        }
        public static bool CheckFieldInTable(DBAccess dbcon, string strFieldName, string strTableName)
        {
            string strSql = "select top 0 * from " + strTableName;


            DataTable dt = dbcon.GetDataSet(strSql).Tables[0];
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Columns[i].ColumnName.Trim().ToUpper() == strFieldName.Trim().ToUpper())
                {
                    return true;
                }
            }
            return false;
        }
        public static void UpdateTable(DBAccess dBAccess, string strFields, string strTableName, DataRow dr, string strKeyFieldsName, bool blnNeedGetTimeStamp)
        {
            UpdateTable(dBAccess, strFields, strTableName, dr, FormStatus.Update, strKeyFieldsName, blnNeedGetTimeStamp);
        }


        #endregion


        public static void AddLog( DBAccess dbcon, LogStruct logInfo)
        {
            string strSQL = string.Empty;
            strSQL = " Insert into mSickTreatLog ( SickRecID,TypeDesc,Type,ExamDate,ExamSeqNo,ExamRecID  )" +
                     " Values ( '{0}','{1}','{2}','{3}','{4}','{5}' )  ";
            strSQL = string.Format(strSQL, logInfo.strSickRecID, logInfo.strTypeDesc, logInfo.titleType, logInfo.datExamDate, logInfo.strExamSeqNo, logInfo.strExamRecID);
            dbcon.ExecuteSql(strSQL); 
        }

        public static void TAGCheck(DBAccess dbcon, string strConName, string strTagNo)
        {
            string strSQL = "Insert into TagCheck ( tagno, conname )" +
                            "values ( '"+ strTagNo +"','"+ strConName +"')";
            dbcon.ExecuteSql(strSQL);
        }




        public  static void OptionShow(System.Windows.Forms.Form  form)
        {    
           
            form.Opacity = 0.98;
        }


        public static string   GetApplication()
        {
            return System.Windows.Forms.Application.StartupPath;
        }
       


    }
}
